# !/usr/bin/env python
# -*- coding:utf-8 -*-
# @FileName  :day1_zy_flask.py
# @Time      :2022/5/18 0018 17:51
# @Author    :何胜利

from flask import Flask
# 导入配置类
from settings import DefaultConfig
import json

import pymysql
app = Flask(__name__)
# 从配置对象中加载配置
app.config.from_object(DefaultConfig)


@app.route("/")
def index():
    # 调用配置
    mysql_host = app.config.get("MYSQL_HOST")
    mysql_port = app.config.get("MYSQL_PORT")
    print("day1作业")

@app.route("/create")
def create():
    # 连接数据库
    con = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="20013021",
                                       database="p7day1_zy", charset="utf8")
    # 创建游标对象
    c1 = con.cursor()

    # 编写sql语句
    sql = """create table stu(
    id int primary key auto_increment,
    name varchar (20),
    age int ,
    gender enum("男","女") default "男"
    );"""

    # 执行sql语句
    c1.execute(sql)
    return "创建成功"

@app.route("/insert")
def insert():
    # 连接数据库
    con = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="20013021",
                          database="p7day1_zy", charset="utf8")
    # 创建游标对象
    c1 = con.cursor()

    # 编辑sql语句
    sql = """insert into stu values(1,'佳球',20,'女'),(2,'孙博',19,'男'),(3,'胜利',21,'男');"""

    # 游侠对象执行spl语句
    ret = c1.execute(sql)
    # 成功提交  失败就回滚
    if ret:
        con.commit()
        return "插入成功"
    else:
        con.rollback()
        return "插入失败"

@app.route("/update")
def update():
    # 连接数据库
    con = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="20013021",
                          database="p7day1_zy", charset="utf8")
    # 创建游标对象
    c1 = con.cursor()

    # 编辑sql语句
    sql = """update stu set gender = '女' where id = 2;"""
    ret = c1.execute(sql)
    if ret:
        con.commit()
        return "修改成功"
    else:
        con.rollback()
        return  "修改失败"

@app.route("/delete")
def delete():
    # 连接数据库
    con = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="20013021",
                          database="p7day1_zy", charset="utf8")
    # 创建游标对象
    c1 = con.cursor()

    # 编辑sql语句
    sql = """delete from stu where id = 2;"""

    ret = c1.execute(sql)

    if ret:
        con.commit()
        return "删除成功"
    else:
        con.rollback()
        return "删除失败"

@app.route("/select")
def select():
    # 连接数据库
    con = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="20013021",
                          database="p7day1_zy", charset="utf8")
    # 创建游标对象
    c1 = con.cursor()

    # 编辑sql语句
    sql = """select * from stu;"""

    c1.execute(sql)
    try:
        select_all = c1.fetchall()
        print(select_all)
    except:
        return '查询失败'
    else:
        return '查询的数据是%s' % json.dumps(select_all)


if __name__ == '__main__':
    app.run()
